﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class ThongKeBaoCaoDAL : BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            throw new NotImplementedException();
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();
        }

        public bool Insert(object obj)
        {
            throw new NotImplementedException();
        }

        public bool Update(object obj)
        {
            throw new NotImplementedException();
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public bool deleteobj(object obj)
        {
            throw new NotImplementedException();
        }


        public DataTable select_thongkecacSP_DN(string id)
        {
            string sql = @" select a.MSDN,b.MASP,b.SL,c.TENSP,d.TENLV,d.MSLV,e.MANGANH,e.TENNGANH 
                        from dn a join SANPHAMDOANHNGHIEP b on a.MSDN=b.MSDN join SANPHAM c on b.MASP=c.MASP join LINHVUC d on c.MALV=d.MSLV join NGANH e on e.MANGANH=d.MANGANH
                        where a.MSDN='{0}' ";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        public DataTable select_report_thongkecacSP_DN(string id)
        {
            string sql = @" select a.MSDN,a.TENDN,a.DC,a.DT,a.FAX,f.MSLV as MSLVDN,f.TENLV as TENLVDN,g.MANGANH as MANGANHDN,g.TENNGANH as TENNGANHDN ,b.MASP,b.SL,c.TENSP,d.TENLV as TENLVSP,d.MSLV as MSLVSP,e.MANGANH as MANGANHSP,e.TENNGANH as TENNGANHSP
                        from dn a join SANPHAMDOANHNGHIEP b on a.MSDN=b.MSDN join SANPHAM c on b.MASP=c.MASP join LINHVUC d on c.MALV=d.MSLV join NGANH e on e.MANGANH=d.MANGANH join LINHVUC f on a.MSLV=f.MSLV join NGANH g on f.MANGANH=g.MANGANH
                        where a.MSDN='{0}' ";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        public DataTable select_thongkeSP_SX(string id,DateTime bd,DateTime kt)
        {
            string sql = @"select a.MSDN,a.TENDN,a.DC,a.DT,a.FAX,f.MSLV as MSLVDN,f.TENLV as TENLVDN,g.MANGANH as MANGANHDN,g.TENNGANH as TENNGANHDN ,b.MASP,b.SOLUONG,b.NGAYBATDAU,b.NGAYKETTHUC,c.TENSP,d.TENLV as TENLVSP,d.MSLV as MSLVSP,e.MANGANH as MANGANHSP,e.TENNGANH as TENNGANHSP,h.NOIDUNG,h.SOGP 
                            from dn a join SANXUAT b on a.MSDN=b.MSDN join SANPHAM c on b.MASP=c.MASP join LINHVUC d on c.MALV=d.MSLV join NGANH e on e.MANGANH=d.MANGANH join LINHVUC f on a.MSLV=f.MSLV join NGANH g on f.MANGANH=g.MANGANH join GPSX h on b.SOGP=h.SOGP
                            where a.MSDN='{0}' and b.NGAYBATDAU <= '{1}' and b.NGAYKETTHUC >='{2}' ";
            sql = string.Format(sql,id,kt,bd);
            return ExecuteQuery(sql);
        }
        public DataTable select_DN()
        {
            string sql = @" select MSDN as id,TENDN as ten from dn ";
            return ExecuteQuery(sql);
        }
        public DataTable select_DNsxSPkhongdungLV()
        {
            string sql = @" select  DISTINCT a.MSDN,a.TENDN,a.DC,a.DT,a.FAX,COUNT(a.MSDN)as sl
                            from DN a join SANPHAMDOANHNGHIEP b on a.MSDN=b.MSDN join SANPHAM c on b.MASP=c.MASP 
                            where a.MSLV<>c.MALV
                            group by a.MSDN,a.TENDN,a.DC,a.DT,a.FAX ";
            return ExecuteQuery(sql);
        }
        public DataTable select_chitietDNsxSPkhongdungLV(string id)
        {
            string sql = @" select   a.MSDN,a.TENDN,a.DC,a.DT,a.FAX,c.MASP,c.TENSP,d.TENLV as TENLVDN,a.MSLV as MSLVDN,e.TENLV as TENLVSP,c.MALV as MSLVSP
                            from DN a join SANPHAMDOANHNGHIEP b on a.MSDN=b.MSDN join SANPHAM c on b.MASP=c.MASP join LINHVUC d on a.MSLV=d.MSLV join LINHVUC e on c.MALV=e.MSLV
                            where a.MSLV<>c.MALV and a.MSDN='{0}'";
            sql = string.Format(sql,id);
            return ExecuteQuery(sql);
        }
    }
}
